package springboot.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class HikariCURDDemo {

    public static void main(String[] args) {
        HikariCURDDemo crudDemo = new HikariCURDDemo();

        System.out.println("CRUDDemo truncate ");
        crudDemo.truncate();

        System.out.println("HikariCURDDemo insert ");
        crudDemo.demo1("t1", 18);
        crudDemo.demo1("t2", 19);
        crudDemo.demo1("t3", 20);
        crudDemo.demo4();

        System.out.println("HikariCURDDemo update ");
        crudDemo.demo3();
        crudDemo.demo4();

        System.out.println("HikariCURDDemo delete ");
        crudDemo.demo2();
        crudDemo.demo4();

        System.out.println("HikariCURDDemo prepareStatement query t2");
        crudDemo.demo5("t2");

    }

    public void truncate(){
        //添加
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = HikariUtils.getConnections();
            //获得语句执行者
            st = conn.createStatement();
            //执行SQL语句
            int r = st.executeUpdate("TRUNCATE TABLE student ");
        } catch (Exception e) {
            throw new RuntimeException(e);

        } finally {
            HikariUtils.closeResource(conn, st, rs);
        }
    }


    public void demo1(String name, int age) {
        //添加
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = HikariUtils.getConnections();
            //获得语句执行者
            st = conn.createStatement();
            //执行SQL语句
            int r = st.executeUpdate("insert into student (name,age)  values ('" + name + "'," + age + ") ");
        } catch (Exception e) {
            throw new RuntimeException(e);

        } finally {
            HikariUtils.closeResource(conn, st, rs);
        }
    }

    public void demo2() {
        //删除
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = HikariUtils.getConnections();
            //获得语句执行者
            st = conn.createStatement();
            //执行SQL语句
            int r = st.executeUpdate("delete from student where name = 't1'");
            System.out.println(r);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            HikariUtils.closeResource(conn, st, rs);
        }
    }

    public void demo3() {
        //修改
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = HikariUtils.getConnections();
            //获得语句执行者
            st = conn.createStatement();
            //执行SQL语句
            int r = st.executeUpdate("update student set age = '20' where name = 't1'");
            System.out.println(r);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            HikariUtils.closeResource(conn, st, rs);
        }
    }

    public void demo4() {
        //查询
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = HikariUtils.getConnections();
            //获得语句执行者
            st = conn.createStatement();
            //执行SQL语句
            rs = st.executeQuery("select * from student");

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                System.out.println(id + "---" + name + "---" + age);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    public void demo5(String nameParam) {
        //查询
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = HikariUtils.getConnections();
            //获得语句执行者
            st = conn.prepareStatement("select * from student where name = ? ");
            //执行SQL语句
            st.setString(1, nameParam);

            rs = st.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                System.out.println(id + "---" + name + "---" + age);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

}
